<?php

class Cron extends Controller {

    function Cron() {
        parent::Controller();
    }

    function mail_me() {
		
        $iSend = 0;
        //scot taskul disponibil
        $sQueryTask = "
            SELECT mailer_task.mailer_task_id, mailer.*
	        FROM mailer_task
	        left join mailer on mailer.mailer_id=mailer_task.mailer_id
	WHERE
	    mailer_task_status='0' and mailer_task_date<now() AND ((mailer_task_delay_date is not null AND  mailer_task_delay_date < now()) OR (mailer_task_delay_date is null or mailer_task_delay_date='' ))
	order by priority asc limit 0,1";
        $aResultTask = $this->db->query($sQueryTask);

        if ($aResultTask->num_rows() > 0) {

            $aTask = $aResultTask->row();
            $iMailsLeft = $aTask->mail_minute; //mailuri de trimis ramase            


            ///////////////////////////////////////////////////////////////////////////////////////////////////////////
            //extrag toate persoanele disponibile pe campania taskului
            $sQueryContactIntern = "
			SELECT client_persons_id, persons.lname, `persons`.`fname`, persons.company,  `extern` , persons.email, persons.email2, persons.sex, IF(persons.unsubscribed = 1, persons.unsubscribed,  client_persons.unsubscribed ) as unsubscribed , create_by, modify_by2, `persons`.`address`, `persons`.`phone`, `persons`.`mobil`, `persons`.`web`, `persons`.`cui`,   D.name AS domain_name, SD.name AS domain_sub_name,  persons.email , persons.email2,
			P.name AS position_name, SP.name AS position_sub_name, persons.employees, county.name as county_name, city.name as city_name
			FROM client_persons
                LEFT JOIN persons ON persons.person_id=client_persons.person_id
                LEFT JOIN domain D ON D.domain_id=persons.domain_id
                LEFT JOIN domain SD ON SD.domain_id=persons.domain_sub_id
                LEFT JOIN position P ON P.position_id=persons.position_id
                LEFT JOIN position SP ON SP.position_id=persons.position_sub_id
                LEFT JOIN county ON county.county_id=persons.county_id
                LEFT JOIN city ON city.city_id=persons.city_id

			WHERE extern=0  AND client_persons.client_id =  {$aTask->client_id}
            ORDER BY client_persons_id ASC  ";
            $aResultContactClientIntern = $this->db->query($sQueryContactIntern)->result();


            $sQueryContactExtern = "
			SELECT client_persons_id,  lname, `fname`, company , `extern`,client_persons.email,client_persons.email2, client_persons.sex,unsubscribed, '1' AS create_by, '1' AS modify_by2 ,
			'' AS address, '' AS phone, '' AS mobil,'' AS web, '' AS cui,  '' AS  domain_name, '' AS   domain_sub_name, email , email2,
			'' AS position_name, '' AS position_sub_name, '' as employees,  '' as county_name, '' as city_name
			 FROM client_persons
			 WHERE extern=1 AND client_persons.client_id =  {$aTask->client_id}                      
            ORDER BY client_persons_id ASC  ";
            $aResultContactClientExtern = $this->db->query($sQueryContactExtern)->result();

            $aResultContactClient = array_merge($aResultContactClientIntern, $aResultContactClientExtern);

            //daca sunt contacte noi incerc sa trimit
            if (count($aResultContactClient) > 0) {

                foreach ($aResultContactClient as $aContact) {
                    //caut sa vad daca exista deja in lista de trimiteri
                    $sContactDuplicatQuery = "select * from mailer_report where mailer_task_id={$aTask->mailer_task_id} and client_persons_id={$aContact->client_persons_id}  ";
                    $aContactDuplicatResult = $this->db->query($sContactDuplicatQuery);
                    //daca nu exista contactul atunci trimit mail
                    if ($aContactDuplicatResult->num_rows() == 0) {

                        //se insereaza ca nou contactul
                        $aTemp = array("mailer_task_id" => $aTask->mailer_task_id,
                                       "client_persons_id" => $aContact->client_persons_id,
                                       "lname" => $aContact->lname,
                                       "fname" => $aContact->fname,
                                       "sex" => $aContact->sex,
                                       "email" => $aContact->email,
                                       "company" => $aContact->company,
                                       "status" => 21,
                                       "descriere" => "netrimis",
                                       "email2" => $aContact->email2,
                                       "status2" => 21,
                                       "descriere2" => "netrimis",
                                       "extern" => $aContact->extern,
                                       "unsubscribed" => $aContact->unsubscribed,
                                       "create_by" => $aContact->create_by,
                                       "modify_by2" => $aContact->modify_by2
                        );
                        $this->db->flush_cache();
                        $this->db->insert("mailer_report", $aTemp);
                        $iMailerReportId = $this->db->insert_id();

                        $iSend = $this->sendMailer($aContact, $aTask, $iMailerReportId);
                        //$iSend=1;
                        $iMailsLeft = $iMailsLeft - $iSend;

                        //daca nu se mai pot trimite mailuri se paraseste functia
                        if ($iMailsLeft <= 0) {
                            return;
                        }
                    }

                    //daca se gaseste contactul in raport atunci se verifica daca exista schimbari la nivel de email; daca exista atunci se retrimite
                    if ($aContactDuplicatResult->num_rows() == 1) {

                        $aContactNew = $aContactDuplicatResult->row();

                        //verificare daca email1 difera
                        //daca difera se face update la status(21)/email..
                        if ($aContactNew->email != $aContact->email) {
                            $aUpdate = array("status" => 21, "email" => $aContact->email);
                            $this->db->flush_cache();
                            $this->db->where("mailer_report_id", $aContactNew->mailer_report_id);
                            $this->db->update("mailer_report", $aUpdate);
                        }

                        //verificare daca email2 difera
                        //daca difera se face update la status(21)/email..
                        if ($aContactNew->email2 != $aContact->email2) {
                            $aUpdate = array("status2" => 21, "email2" => $aContact->email2);
                            $this->db->flush_cache();
                            $this->db->where("mailer_report_id", $aContactNew->mailer_report_id);
                            $this->db->update("mailer_report", $aUpdate);
                        }
                    }
                }
            }

            ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            //incerc sa retrimit la contactele cu problema
            $sQueryContact = "select  * from mailer_report  where  mailer_task_id={$aTask->mailer_task_id}  and ( status=21 or status2=21 )  ORDER BY lname ASC ";
            $aResultContactClient = $this->db->query($sQueryContact)->result();
            foreach ($aResultContactClient as $aContact) {

                //incerc sa retrimit doar daca exista si in baza de date
                $this->db->flush_cache();
                $this->db->where("client_persons_id", $aContact->client_persons_id);
                $iCount = $this->db->get("client_persons")->num_rows();
                if ($iCount == 0) {
                    continue;
                }

                $iSend = $this->sendMailer($aContact, $aTask, $aContact->mailer_report_id);
                $iMailsLeft = $iMailsLeft - $iSend;
                //daca nu se mai pot trimite mailuri se paraseste functia
                if ($iMailsLeft <= 0) {
                    return;
                }
            }
        }

        //daca s-a terminat de verificat si numarul de trimiteri ramase e > 0 taskul se va termina
        if ($iMailsLeft > 0) {
            $this->db->flush_cache();
            $this->db->where("mailer_task_id", $aTask->mailer_task_id);
            $this->db->set("mailer_task_status", 10);
            $this->db->update("mailer_task");
        }
		

    }

    /**
     * functie care trimite mailuri unui contact din query-ul $sQueryContact
     * @param array $aContact datele unui contact  (client_persons_id, persons.lname, `persons`.`fname`,  `extern` , persons.email, persons.email2, persons.sex,unsubscribed)
     * @param array $aTask contine datele mailerului si id-ul de task
     * @param integer $iMailerReportId - contine numarul de inregistrare din tabelul mailer_report
     * @return integer  $iSend
     */
    function sendMailer($aContact, $aTask, $iMailerReportId) {

        $aEmployees = $this->config->item("employeeContact");
        $path_full = $this->config->item('upload_path_full');
        $iSend = 0;
        //pun status de dezabonare
        if ($aContact->unsubscribed == 1) {
            $aTemp = array("status" => 11, "status2" => 11, "descriere" => "dezabonat", "descriere2" => "dezabonat");
            $this->db->flush_cache();
            $this->db->where("mailer_report_id", $iMailerReportId);
            $this->db->update("mailer_report", $aTemp);
            return $iSend;
        }




        $this->load->model("model_mailer");
        $this->load->plugin('phpmailer');
        $mail = new phpmailer(true);

        $mail->IsHTML(true);
        $mail->CharSet = "utf-8";
        $mail->IsSMTP();
        $mail->SMTPAuth = true;
		
		$mail->Host = "mail.morethanpub.ro";
			$mail->Password = "officeoffice";
			$mail->Username = "office@morethanpub.ro";
		
		/*$mail->Host = "comunicatedirectmarketing.ro";
		$mail->Password = "marketing2006";
		$mail->Username = "office@comunicatedirectmarketing.ro";
		*/
			
			/*$mail->Password = "bookbook2013";
			$mail->Username = "bookland@comunicatedirectmarketing.ro";    */					
        $mail->Port = 26;
        

        $body = $aTask->body;

	     //se preia id-ul de mailer
	    $this->load->model("model_mailer");
        $resultLink = $this->model_mailer->bringLink("mailer_link", $aTask->mailer_id, 0, 1000, "link_nume", "ASC");
        $resultLink = $resultLink["DS_LINK"];
        $satelit = $this->config->item("mtp_satelit");
		$satelitDezabonare = $this->config->item("mtp_satelit_dezabonare");

        foreach ($resultLink as $key => $aLink) {
	        $aLinkEncoded = base64_encode($aLink["link_nume"] . "-**-" . $aContact->client_persons_id . "-**-" . $aLink["link_id"] . "-**-" . md5($aLink["link_id"] + ($aContact->client_persons_id * 22)));
			$aLinkEncoded = str_replace("/", "_", $aLinkEncoded);
	        $body = str_replace($aLink["link_cod"], $satelit . $aLinkEncoded, $body);
        }
		
		//creare link dezabonare
		$aLinkDezabonare= base64_encode($aContact->client_persons_id . "-**-" . md5($aContact->client_persons_id * 22));
		$aLinkDezabonare = str_replace("/", "_", $aLinkDezabonare);
		$aLinkDezabonare = $satelitDezabonare . $aLinkDezabonare;		

        $name = $aContact->fname . " " . $aContact->lname;

        if ($aContact->sex == "2") {
            $header_lower = strtolower($aTask->header_m) . " " . $name;
            $header_title = ucfirst($aTask->header_m) . " " . $name;
            $header_ = $aTask->header_m . " " . $name;
        }
        elseif ($aContact->sex == "1") {
            $header_lower = strtolower($aTask->header_f) . " " . $name;
            $header_title = ucfirst($aTask->header_f) . " " . $name;
            $header_ = $aTask->header_f . " " . $name;
        }
        else {
            $header_lower = strtolower($aTask->header_n) . " " . $name;
            $header_title = ucfirst($aTask->header_n) . " " . $name;
            $header_ = $aTask->header_n . " " . $name;
        }


       //replacing customizari nume
        $strReplace1 = array("LOWER_HEADER", "TITLE_HEADER", "NAME", "_HEADER", "_DISCLAIMER", "_COMPANY", "_ADDRESS","_PHONE", "_MOBIL",  "_WEB", "_CUI", "_DOMAIN", "_SUBDOMAIN", "_EMAIL1", "_EMAIL2","_POSITION","_SUBPOSITION", "_EMPLOYEE", "_COUNTY", "_CITY", "PRENUME", "NUME", "_DEZABONAT");
        $strReplace2 = array($header_lower, $header_title, $name, $header_, $aTask->disclaimer, $aContact->company, $aContact->address, $aContact->phone, $aContact->mobil, $aContact->web, $aContact->cui, $aContact->domain_name, $aContact->domain_sub_name,  $aContact->email,  $aContact->email2,  $aContact->position_name, $aContact->position_sub_name, $aEmployees[ $aContact->employees], $aContact->county_name, $aContact->city_name, $aContact->fname,  $aContact->lname, $aLinkDezabonare  );

        $body = str_replace($strReplace1, $strReplace2, $body);
		$mail->Subject = str_replace($strReplace1, $strReplace2, $aTask->subject);		
	

        $aAttachment = $this->model_mailer->bringAttach("mailer_attach", $aTask->mailer_id, "attach", 0, 1000, "name", "asc");
        foreach ($aAttachment["DS_ATTACH"] as $attach) {

            if ($attach["name"] != '') {
                $filename = "././resources/uploads/attach/" . $attach["name"];
                if (file_exists($filename)) {
                    $mail->AddAttachment($filename);
                }
                else {
                    $aResponse = array("error" => true, "description" => "Un atasament nu exista", "error_no" => "2");
                }
            }
        }


        //imagini embedded
        $aAttachment = $this->model_mailer->bringAttach("mailer_attach", $aTask->mailer_id, "image", 0, 1000, "name", "asc");
        foreach ($aAttachment["DS_IMAGE"] as $attach) {
            if ($attach["name"] != '') {
                $filename = "././resources/uploads/full/" . $attach["name"];
                if (file_exists($filename)) {
                    $pattern = '/' . $attach["name"] . '/';
                    preg_match_all($pattern, $body, $matches);

                    if (count($matches[0]) > 0) {
                        //scot calea relativa
                        $body = str_replace($path_full, "", $body);
                        //$sReplace="cid:".$attach["name"];
                        $aReplace = explode(".", $attach["name"]);
                        array_pop($aReplace);
                        $base = implode(".", $aReplace);
                        $sReplace2 = "cid:" . $base;

                        $body = str_replace($attach["name"], $sReplace2, $body);
                        $mail->AddEmbeddedImage($filename, $base, $attach["name"]);
                    }

                }
                else {
                    $aResponse = array("error" => true, "description" => "Un atasament nu exista", "error_no" => "2");
                }
            }

        }

        $mail->AddReplyTo($aTask->reply_email, $aTask->reply_name);
        $mail->SetFrom($aTask->from_email, $aTask->from_name);



        if ($aResponse["error"] === TRUE) {
            $aTemp = array("status" => $aResponse["error_no"], "status2" => $aResponse["error_no"], "descriere" => $aResponse["description"], "descriere2" => $aResponse["description"]);
            $this->db->flush_cache();
            $this->db->where("mailer_report_id", $iMailerReportId);
            $this->db->update("mailer_report", $aTemp);
            return $iSend;
        }

  
		$sBodyStyle  = $aTask->body_style ;      

        if ($aContact->status != 100) {

            if (($aContact->email != "") && (($aTask->mail_type == 1) || $aTask->mail_type == 3 || $aTask->mail_type == 4 || (($aTask->mail_type == 5 && $aContact->email2 == "")))) {
                try {
                          
                    $mail->Body = "<body {$sBodyStyle} >" . $body . $img1 . "</body>";

                    //sleep(4);
                    $mail->AddAddress($aContact->email); //You can add more emails

                   if (!$mail->Send()) {
                        // if (!TRUE) {
                        $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: " . $mail->ErrorInfo, "error_no" => 3);
                    }
                    else {
                        $aResponse = array("error" => false, "description" => "Emailul a fost trimis", "error_no" => 100, "send_date" => date("Y-m-d H:i:s"));
                        $iSend++;
                    }

                    $aTemp = array("status" => $aResponse["error_no"], "descriere" => $aResponse["description"], "send_date" => $aResponse["send_date"], "prenume_personalizat"=>$aContact->fname,  "nume_personalizat"=>$aContact->lname, "subiect_personalizat"=>$mail->Subject);
                    $this->db->flush_cache();
                    $this->db->where("mailer_report_id", $iMailerReportId);
                    $this->db->update("mailer_report", $aTemp);
                }
                catch (phpmailerException $e) {
                    $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: " . $e->errorMessage(), "error_no" => "12");
                }
                catch (Exception $e) {
                    $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: " . $e->getMessage(), "error_no" => "12");
                }

                if ($e) {
                    $aTemp = array("status" => $aResponse["error_no"], "descriere" => $aResponse["description"]);
                    $this->db->flush_cache();
                    $this->db->where("mailer_report_id", $iMailerReportId);
                    $this->db->update("mailer_report", $aTemp);
                }
            }
            else {
                $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: constrangeri tip mail sau email nul", "error_no" => 12);
                $aTemp = array("status" => $aResponse["error_no"], "descriere" => $aResponse["description"]);
                $this->db->flush_cache();
                $this->db->where("mailer_report_id", $iMailerReportId);
                $this->db->update("mailer_report", $aTemp);
            }
        }

        if ($aContact->status2 != 100) {
            if (($aContact->email2 != "") && (($aTask->mail_type == 2) || $aTask->mail_type == 3 || $aTask->mail_type == 5 || (($aTask->mail_type == 4 && $aContact->email == "")))) {
                try {                   
                                  
                    $mail->ClearAllRecipients();
                    $mail->Body = "<body $sBodyStyle>" . $body . $img2 . "</body>";
                    //sleep(1);
                    $mail->AddAddress($aContact->email2); //You can add more emails

                    if (!$mail->Send()) {
                        //if (!TRUE) {
                        $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: " . $mail->ErrorInfo, "error_no" => 3);
                    }
                    else {
                        $aResponse = array("error" => false, "description" => "Emailul a fost trimis", "error_no" => 100, "send_date" => date("Y-m-d H:i:s"));
                        $iSend++;
                    }

                    $aTemp = array("status2" => $aResponse["error_no"], "descriere2" => $aResponse["description"], "send_date2" => $aResponse["send_date"], "prenume_personalizat"=>$aContact->fname,  "nume_personalizat"=>$aContact->lname,"subiect_personalizat"=>$mail->Subject);
                    $this->db->flush_cache();
                    $this->db->where("mailer_report_id", $iMailerReportId);
                    $this->db->update("mailer_report", $aTemp);
                }
                catch (phpmailerException $e) {
                    $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: " . $e->errorMessage(), "error_no" => "12");
                }
                catch (Exception $e) {
                    $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: " . $e->getMessage(), "error_no" => "12");
                }

                if ($e) {
                    $aTemp = array("status2" => $aResponse["error_no"], "descriere2" => $aResponse["description"]);
                    $this->db->flush_cache();
                    $this->db->where("mailer_report_id", $iMailerReportId);
                    $this->db->update("mailer_report", $aTemp);
                }
            }
            else {
                $aResponse = array("error" => true, "description" => "Emailul NU a fost trimis: constrangeri tip mail sau email nul", "error_no" => 12);
                $aTemp = array("status2" => $aResponse["error_no"], "descriere2" => $aResponse["description"]);
                $this->db->flush_cache();
                $this->db->where("mailer_report_id", $iMailerReportId);
                $this->db->update("mailer_report", $aTemp);
            }
        }

        return $iSend;
    }


    function sync() { 
		//set POST variables
		$fields_string = null;
		$url = $this->config->item("mtp_satelit_return");
		$url_dezabonare = $this->config->item("mtp_satelit_dezabonare_return");
		$fields = array('lname' => "mumu");

		//url-ify the data for the POST
		foreach ($fields as $key => $value) {
			$fields_string .= $key . '=' . $value . '&';
		}
		rtrim($fields_string, '&');

		//open connection
		$ch = curl_init();

		//set the url, number of POST vars, POST data
		curl_setopt($ch, CURLOPT_URL, $url);
		curl_setopt($ch, CURLOPT_POST, count($fields));
		curl_setopt($ch, CURLOPT_POSTFIELDS, $fields_string);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

		//execute post
		$result = curl_exec($ch);
		//close connection
		curl_close($ch);
		$result = json_decode($result, true);
		
		foreach ($result as $record) {
			$query = "INSERT INTO mailer_link_report (id_satelit,link_id,link_nume,link_data_acces ,client_person_id) VALUES (?,?,?,?,?)  ON DUPLICATE KEY UPDATE id_satelit=?;";
			$this->db->query($query, array(
			                              $record["id"], $record["link_id"], $record["link_nume"],
			                              $record["link_data_acces"], $record["client_person_id"], $record["id"]));
		}
		
		//open connection
		$ch = curl_init();		
		//set the url, number of POST vars, POST data
		curl_setopt($ch, CURLOPT_URL, $url_dezabonare);
		curl_setopt($ch, CURLOPT_POST, count($fields));
		curl_setopt($ch, CURLOPT_POSTFIELDS, $fields_string);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

		//execute post
		$result = curl_exec($ch);
		//close connection
		curl_close($ch);
		$result = json_decode($result, true);

		$query = "update client_persons set unsubscribed = 1 where client_persons_id = ? ";
		foreach ($result as $record) {			
			$this->db->query($query, array($record["client_person_id"]));
		}		
	}


	function backup() {
		//scoatere date de backup din fisierul config.php
		$sBackupDir = $this->config->item("backup_dir");
		$sBackupDb = $this->config->item("backup_db");
		$iBackupNr = $this->config->item("backup_nr");

		//selectez inregistrarea asociata zilei curente (daca exista)
		$query = $this->db->query("select * from backup where date(`date`)=date(now())");
		if ($query->num_rows() == 0) {

			$this->db->limit("1", "0");
			$this->db->order_by("backup_id", "desc");
			$query = $this->db->get("backup");

			//scot numarul asociat ultimului backup
			$iLastNr = $query->row()->nr;

			//daca nu exista nici o inregistrare, $iLastNr devine 1
			if ($iLastNr == '') {
				$iLastNr = 1;
			}
			else {
				$iLastNr++;
			}

			//daca se depaseste limita backup-uri se revine la iteratia 1
			if ($iLastNr == $iBackupNr) $iLastNr = 1;
			//inserez in baza de date ziua si numarul backup-ului
			$this->db->query("insert into backup (date,nr) values (now(),'" . $iLastNr . "')");
			$iRec = $this->db->insert_id();
			$command = "F:/wamp/bin/mysql/mysql5.5.8/bin/mysqldump --opt -h localhost -u root " . $sBackupDb . " --default-character-set=utf8 --add-drop-table --complete-insert  > " . $sBackupDir . "backup_" . $iLastNr . ".sql";
			echo $command;
			system($command);

			$this->db->set("date_end", date("Y-m-d H:i:s"));
			$this->db->where("backup_id", $iRec);
			$this->db->update("backup");

		}

		echo "done";
	}

}